import datetime

from django.http import HttpResponse
from django.shortcuts import render

from article.models import User, Article

from datetime import datetime


# Create your views here.

def article_test(request):
    # 新增
    # user = User(username='test', password='abc123')
    # user.save()
    # article = Article(title="2025就业形势", content='非常困难', author=user)
    # article.save()

    # 查找(文章带出作着)
    # article = Article.objects.first()
    # print(f"--{article.title}--{article.content}--{article.author.username}--{article.author.password}")

    # 查找(文章带出作着)
    user = User.objects.first()
    set_all = user.article_set.all()
    for article in set_all:
        print(f"{article.title}---{article.content}--{article.author.username}--{article.author.password}")
    return HttpResponse("文章内容操作成功")


def one_to_many(request):
    user = User.objects.first()
    user.article_set.filter(author=user)
    set_all = user.article_set.filter(title__contains='2025').all()
    for article in set_all:
        print(f"-{article.title}---{article.content}--{article.author.username}")
    return HttpResponse("成功！")


def query1(request):
    # sql中的like语句
    # SELECT * FROM `article_article` WHERE `article_article`.`id` = 1
    # querySet = Article.objects.filter(id__exact='1')
    # # 查询结果.query看到，article.query，就可以看到底层执行的sql语句
    # article = querySet.first()
    # print(querySet.query)

    # 忽略大小写的精确查找，sql用的是like，但是不是 like %keyword%
    #  SELECT*FROM `article_article` WHERE title LIKE 2025  和  like %2025% 不一样， 这个只是忽略大小写
    query_set = Article.objects.filter(title__iexact='Chatgpt')
    print(query_set.query)
    article = query_set.first()
    if article:
        print(f"{article.title}---{article.content}--{article.author.username}")
    return HttpResponse("query1")


def query2(request):
    # 模糊查询
    # query_set = Article.objects.filter(title__contains='Chat')
    # 模糊查询（忽略大小写）
    query_set = Article.objects.filter(title__icontains='chat')
    print(f"sql: {query_set.query}")
    set_all = query_set.all()
    print(f"数据: {set_all}")
    if set_all:
        for article in set_all:
            print(f"{article.title}---{article.content}--{article.author.username}")

    return HttpResponse("query2")


"""
类似的 
in 、 gt 、gte 、lt 、 lte 、 startswith 、 istartswith 、 endswith 、 iendswith 、range
"""


def query3(request):
    # SELECT `article_article`.`id`, `article_article`.`title`, `article_article`.`content`, `article_article`.`author_id` FROM `article_article` WHERE `article_article`.`id` IN (1, 2, 3)
    query_set = Article.objects.filter(id__in=[1, 2, 3])
    print(f"sql: {query_set.query}")
    set_all = query_set.all()
    if set_all:
        for article in set_all:
            print(f"{article.title}---{article.content}--{article.author.username}")
    return HttpResponse("query3")


"""
类似的 
pub_time__year:  year 、  month 、 day 、 week_day 、time 、 isnull
"""


def query4(request):
    start_date = datetime(year=2025, month=3, day=29)
    end_date = datetime(year=2025, month=3, day=31, hour=23, minute=59, second=59)
    # query_set = Article.objects.filter(pub_time__range=(start_date, end_date))
    query_set = Article.objects.filter(pub_time__year=2025)
    # SELECT `article_article`.`id`, `article_article`.`title`, `article_article`.`content`, `article_article`.`pub_time`, `article_article`.`author_id` FROM `article_article` WHERE `article_article`.`pub_time` BETWEEN 2025-03-29 00:00:00 AND 2025-03-31 23:59:59
    print(f"sql: {query_set.query}")
    set_all = query_set.all()
    if set_all:
        for article in set_all:
            print(f"{article.title}---{article.content}--{article.author.username}")
    return HttpResponse("query4")




def query5(request):
    # 查找标题中，包含chat的文章的用户
    query_set = User.objects.filter(article__title__icontains='chat')
    #SELECT `article_user`.`id`, `article_user`.`username`, `article_user`.`password` FROM `article_user` INNER JOIN `article_article` ON (`article_user`.`id` = `article_article`.`author_id`) WHERE `article_article`.`title` LIKE %chat%
    print(f"sql: {query_set.query}")
    set_all = query_set.all()
    if set_all:
        for user in set_all:
            print(f"{user.password}--{user.username}")
    return HttpResponse("query5")